import sqlite3
import json
import datetime

class DataBase:
    def __init__(self, path = "./plave_data.db"):
        self.conn = sqlite3.connect(path)
        self.cursor = self.conn.cursor()
        self.createTable_Record()
        self.createTable_User()
        self.createTable_Price()
    
    # 创建用户表
    def createTable_User(self):
        sql_table="""
        create table if not exists User(
            uid integer,
            username varchar(50),
            password varchar(50),
            primary key (uid)
        );
        """
        self.cursor.execute(sql_table)
    
    # 创建记录表
    def createTable_Record(self):
        sql_table="""
        create table if not exists Record(
            rid integer,
            uid smallint,
            amount float,
            list json,
            rec_time TIMESTAMP,
            primary key (rid)
        );
        """
        self.cursor.execute(sql_table)

    # 创建价格表
    def createTable_Price(self):
        sql_table="""
        create table if not exists Price(
            pid integer,
            dish_name varchar(50),
            price float,
            primary key (pid)
        );
        """
        self.cursor.execute(sql_table)

    # 插入Price表
    def insert_price(self, data):
        instert_sql = f"""
        insert into Price(dish_name, price)
        VALUES (?, ?)
        """
        self.cursor.executemany(instert_sql, data)
        self.conn.commit()

    # 插入用户表
    def insert_user(self, data):
        select_sql = f"""
        select username 
        from 'User'
        where username='{data[0]}'
        """
        user = []
        for row in self.cursor.execute(select_sql):
            user.append(row)
        
        if len(user) == 0:
            instert_sql = """
            insert into User(username, password) 
            VALUES (?, ?)
            """
            self.cursor.execute(instert_sql, data)
            self.conn.commit()
            return True

        else:
            return False

    # 插入记录表
    def insert_record(self, data):
        instert_sql = """
        insert into Record (uid, amount, list, rec_time) 
        VALUES (?, ?, ?, ?)
        """
        self.cursor.execute(instert_sql, data)
        self.conn.commit()

    # 查询所有表数据
    def select_all(self, tablename):

        select_sql = f"""
        select * from {tablename}
        """
        self.cursor.execute(select_sql)
        result = self.cursor.fetchall()
        row = len(result)
        print(result)
        return result, row
    
    # 登录
    def login(self, username, password):
        sql = f"""
        select * from 'User' 
        where username='{username}'
        and password='{password}'
        """
        user = []
        for row in self.cursor.execute(sql):
            user.append(row)
        
        if len(user) == 0:
            return False, user
        
        return True, user
    
    def close(self):
        self.conn.close()

if __name__ == "__main__":
    data = DataBase()
    currentDateTime = datetime.datetime.now()
    record_item = (1, 12.0, json.dumps([{"rice": 1},{"dish1": 1},{"dish2": 1},{"egg": 1},{"fish": 1}]), currentDateTime)
    data.insert_record(record_item)
    user_item = ("caixinpeng", "020709")
    data.insert_user(user_item)
    price_item = [("rice", 1),("dish1", 5),("egg", 5),("dish2", 3),("fish", 5)]
    data.insert_price(price_item)
    data.select_all("Price")


    
        
